package ca.athabascau.quizmaster.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

public class DataAccess {

	private Connection dataconnection;
	private Comp667ProjectSettings settings;

	// private DataAccess

	public DataAccess() {
		settings = new Comp667ProjectSettings();

	}

	private Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(settings.getDatabaseLocation());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	@SuppressWarnings("finally")
	public ArrayList<Answer> getAnswers() throws SQLException {
		ArrayList<Answer> answers = new ArrayList<Answer>();
		Connection conn = null;
		PreparedStatement getAnswersQuery = null;
		ResultSet results;

		try {
			conn = getConnection();
			// acknowledged that this is clearly a sql injection risk but in the
			// context of the assignment it is a quick and fast solution
			getAnswersQuery = conn.prepareStatement(settings
					.getLoadAllAnwersQuery());
			results = getAnswersQuery.executeQuery();
			while (results.next()) {
				answers.add(new Answer(
						new Question(
								results.getInt("qid"),
								results.getString("text"),
								results.getInt("correctanswernumber"),
								new BaseCodeTable(QuizMasterCodeTables.FIELD,
										results.getInt("FIELD_id"), results
												.getString("FIELD_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.DIFFICULTY_LEVEL,
										results.getInt("DIFFICULTY_LEVEL_id"),
										results.getString("DIFFICULTY_LEVEL_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name")),
								new BaseCodeTable(
										QuizMasterCodeTables.LEARNING_OBJECT_TYPE,
										results.getInt("LEARNING_OBJECT_TYPE_id"),
										results.getString("LEARNING_OBJECT_TYPE_name"))),
						results.getInt("answerno"), results
								.getString("answertext"), results
								.getString("answerid")));

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (getAnswersQuery != null) {
				getAnswersQuery.close();
			}

			return answers;

		}
	}

	public static void main(String[] args) {
		DataAccess da = new DataAccess();

		ArrayList<Answer> answers = null;
		try {
			answers = da.getAnswers();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		for (Answer answer : answers)
			System.out.println(answer.getAnswerId());
	}
}
